How to Use the Navigation Where Clause
The Where clause enables you to filter query data and displays only the data that meets the criteria. You can combine multiple expressions in a Where clause by using the logical operators such as And, Or, AndAlso, OrElse, Is, and IsNot.
Most couples commence with a obvious vision along with a plan, and in some way get pushed off course. online generic cialis https://unica-web.com/members/croatie.html READ ABOUT MORE SUPERFOODS HERE: Your viagra canada pharmacy age, health status, and how you feel about your relationship have a big impact in your sex life. However, it is not possible to find out traditional toilets those are meant to be in squatting style while online cialis mastercard performing evacuation. Whatsoever, men can take Kamdeepak capsule to buy generic viagra remove the symptoms.
Using the Navigation Where Clause for Task Due
In this how to, I will demonstrate how to use the Navigation Where Clause on the navigation button of the Navigation Form. By using the Where Clause, we will use only one datasheet form. We don’t have to create many datasheet forms for each navigation button. The less form you have, the small size of database you will have. The picture below is the datasheet form “TaskDue_DS” that I will use in this how to.
Example of Task Due Today
In this example, I have 4 buttons on my Navigation Form. All buttons will have the TaskDue_DS datasheet form as a target. The first button is the Task due today. After we click this button, the datasheet will display all tasks which are due today.
The Duedate is a field that holds the due date of each task. The Date() is a system function that returns the today date. The expression “[Duedate] = Date()” is the criteria that is assigned to the Navigation Where Clause as shown below.
The result of Task due today below (Example: today date is 8/29/2020)
Example of Task Overdue
The Navigation Target is the TaskDue_DS datasheet form. To get the task overdue, we need the criteria that the due date of task is less than today date and has not yet been completed. So the criteria for the Navigation Where Clause is “duedate < Date() and FinishDate is null” as shown in the picture below.
Result of Task Overdue:
Example of Task due in 10 days
We will use the same TaskDue_DS datasheet form as the target of the Task due in 10 days button. For this button, we want to display all tasks that will due in 10 days, starting from tomorrow to 10 days from now. And also those tasks have not been completed yet. So the Navigation Where Clause will be “duedate > Date() and duedate < Date()+10 and FinishDate is null” as shown in the picture below. We use the Operator “AND” to combine the 3 criterias for the Navigation Where Clause.
Result of Task due in 10 days (Ex: Date() = 8/29/2020)
Example of Task due next month
We want to display all tasks that have the due date in next month. So the Navigation Where Clause will be “month([duedate]) = Month(DateAdd(“m”, 1, Date()))” as shown in the picture below.
- month([duedate]) will return the month of duedate in a number (Ex: 9/5/2020 returns 9)
- Month(Date()) will return the month of today date in a number (Ex: 9/5/2020 returns 9)
- Month(#9/5/2020#)+1 will return 10, but Month(#12/5/2020#)+1 will return 13. So we cannot just use Month([duedate]+1 to get next month
- DateAdd(“m”, 1, Date()) will return the date of 1 month from today date (Ex: 9/5/2020 returns 10/5/2020, but 1/31/2020 returns 2/29/2020)
- So we need to combine the Month() and DateAdd() functions as Month(DateAdd(“m”, 1, Date())). If today date is 9/5/2020, it will return 10. If today date is 12/5/2020, it will return 1. So if today date is 12/5/2020, the Task due next month button will display all tasks that are due in January 2021.
Result of Task due next month (Ex: Date() = 8/29/2020)
Using the Navigation Where Clause for Job Assignment
In this section, I will use the Where Clause to display data on the JobAssign_DS datasheet form. There are many fields in this datasheet form that we can use for the criteria for the Navigation buttons as shown in the picture below.
In the job assignment database, if the users want to view their own job assignments, we need to set up the user’s LoginID field in the user table. The LoginID can be the computer login name or the username from the login form. In the picture below, I have the LoginID field on my Employee table.
In this example, I will use the Environ(“UserName”) function to capture the computer LoginID when each employee opens this database. In the job assignment table, we will assign each job to the unique ID of each employee which is the EmpID, but display as the employee name in the job assignment table. We need to get the EmpID by using the GetUserID() function in the picture below. In order to make this function working, we must assign the LoginID to each employee in the Employee table as shown in the picture above.
Example of All My Jobs
We will use the JobAssign_DS datasheet form as the target of the All My Jobs button. My current computer login name is tewan. It is assigned to the LoginID of the employee name Te Thong on the Employee table above. If I have some jobs/assignments, it will display all my jobs/assignments after I click the All My Jobs button. So the Navigation Where Clause is “[AssignedTo] = GetUserID()” as shown in the picture below. The AssignedTo field in the job assignment table is a lookup field that links to the EmpID of the employee.
My current computer login name = tewan
My employee name = Te Thong
My EmpID = 6
My employee LoginID = tewan
Result of All My Jobs
Example of My New Assignment
We will use the same JobAssign_DS datasheet form as the target of the My New Assignment button. This button will display all my new assignments which have not been completed yet. The AssignedTo field will display my employee name, Te Thong, and the CompletedDate field is blank. So the Navigation Where Clause will be “[assignedto] = GetUserID() And [completedDate] is null” as shown in the picture below.
Result of My New Assignment
Example of All My Assignment Today
This button will display all my assignments that are assigned to me today. If today date is 8/29/2020, the AssignedDate will be 8/29/2020 and the AssignedTo will be Te Thong. So the Navigation Where Clause will be “[assignedto] = GetUserID() And AssignedDate = Date()” as shown in the picture below.
Result of All My Assignment Today
Example of My Job Completed Today
This button will display all my assignments that are completed today. If today date is 8/29/2020, the CompletedDate will be 8/29/2020 and the AssignedTo will be Te Thong. So the Navigation Where Clause will be [assignedto] = GetUserID() And completedDate = Date() and [Result] =”Completed” as shown in the picture below. You may not need and [Result] =”Completed”since you already have completedDate = Date().
Result of My Job Completed Today
